﻿/**
//  CustomerEntity.cs
//  Marquee Moment
//  Copyright 2011 Systematix Technocrates. All rights reserved.
**/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NLog;

namespace MarqueeMoment.Entities
{
    /// <summary>
    /// This is the class to keep all the transaction method from tblCustomer
    /// </summary>
    public class CustomerEntity
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        #region Fields

        private Int32 customerId;
        private String customerName;


        private List<CustomerEntity> customerList;

        #endregion Fields

        #region Constructors

        public CustomerEntity()
            : base()
        {
            customerId = 0;
            customerName = String.Empty;
            customerList = new List<CustomerEntity>();
        }


        #endregion Constructors

        #region Properties

        public Int32 CustomerId
        {
            get { return customerId; }
            set { customerId = value; }
        }

        public String CustomerName
        {
            get { return customerName; }
            set { customerName = value; }
        }

       

        #endregion Properties

        #region Methods

        #region Public

        #endregion Public

        #region Private

        #endregion Private

        #endregion Methods

        #region Static methods

        #region Public
        /// <summary>
        /// To select the records from the Db by using particular word
        /// </summary>
        /// <param name="word">Word is passed by user to select the rows from the table</param>
        /// <returns></returns>

        public static DataSet SelectCustomerBykeyword(string word)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectCustomerbyWord]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@word",word);
           
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    return dataSet;
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select Customer");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null ;
        }
        /// <summary>
        /// To select all the records from the tblCustomer table.
        /// </summary>
        /// <returns></returns>
        public static List<CustomerEntity> SelectAll()
        {
            List<CustomerEntity> customerList = null;
            CustomerEntity customer = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllCustomer]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        customerList = new List<CustomerEntity>();
                        for (int count = 0; count < dataSet.Tables[0].Rows.Count; count++)
                        {
                            customer = new CustomerEntity();
                            customer.CustomerId = Convert.ToInt32(dataSet.Tables[0].Rows[count][tblCustomer.CustomerId]);
                            customer.CustomerName = Convert.ToString(dataSet.Tables[0].Rows[count][tblCustomer.CustomerName]);

                            customerList.Add(customer);
                        }
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Customer");              
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
                
            }
            return customerList;
        }
        /// <summary>
        /// To select all the records from tblCustomer and return Dataset of the records.
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAllCustomer()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllCustomer]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                 return dataSet;
                   
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Customer");      
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }

        /// <summary>
        /// To delete the records of the tblCustomer table.
        /// </summary>
        /// <param name="customerId">Customer Id is the Id to delete the Particular Record. /param>
        public static void Delete(int customerId)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spDeleteCustomer]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblCustomer.CustomerId, customerId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Delete Customer");      
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }
        /// <summary>
        /// To update the record of the tblcustomer
        /// </summary>
        /// <param name="customer">This contains the customer Id to update the record</param>
        public static void Update(CustomerEntity customer)
        {
            
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spUpdateCustomer]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblCustomer.CustomerId, customer.CustomerId);
            cmd.Parameters.AddWithValue("@" + tblCustomer.CustomerName, customer.CustomerName);
           
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Update Customer");  
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }
        /// <summary>
        /// To insert the record in tblCustomer
        /// </summary>
        /// <param name="customer">It contains the information to insert the record i.e name of the customer</param>
        public static void Insert(CustomerEntity customer)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spInsertCustomer]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblCustomer.CustomerName, customer.CustomerName);
           
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Insert");  
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }

        /// <summary>
        /// To count the total record of the tblCustomer
        /// </summary>
        /// <returns></returns>
        public static Int32 SelectRowCountCustomer()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return 0;

            cmd.CommandText = "[spSelectRowCountCustomer]";
            cmd.CommandType = CommandType.StoredProcedure;
            #endregion Initialization

            try
            {
                return (int)cmd.ExecuteScalar();
            }

            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Count record of tblCustomer");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return 0;
        }

        /// <summary>
        /// To count the total record of the tblCustomer
        /// </summary>
        /// <returns></returns>
        public static Int32 SelectRowCountCustomerByFilter(String filterName)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return 0;

            cmd.CommandText = "[spSelectRowCountCustomerByFilter]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("filterName", filterName.Trim());
            #endregion Initialization

            try
            {
                return (int)cmd.ExecuteScalar();
            }

            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Count record of tblCustomer");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return 0;
        }


        /// <summary>
        /// To select the customer from tblCustomer table
        /// </summary>
        /// <param name="sqlQuery">It contains the sqlquary to select the records from the table</param>
        /// <returns></returns>
        public static DataSet SelectSQLCustomer(String sqlQuery)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = sqlQuery;
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                return dataSet;
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select Customer");  
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }
        /// <summary>
        /// Select CustomerName and CustomerId from tblCustomer
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectAllCustomerNameWithId()
        {
            DataTable dt = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            DataSet dataSet = new DataSet();

            cmd.Connection = Connection.GetConnection();
            cmd.CommandText = "[spSelectAllCustomerNameWithId]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        dt = dataSet.Tables[0];

                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All CustomerName With Id");  
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return dt;
        }



        /// <summary>
        /// To not to save the duplicate record in the tblCustomer table
        /// </summary>
        /// <param name="customer">This contains the information of user like CustomerName and CustomerIdId</param>
        /// <returns>true or false</returns>
        public static bool IsExistCustomer(CustomerEntity customer)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            cmd.CommandText = "[spSelectExistingCustomer]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblCustomer.CustomerName, customer.customerName.Trim());
            cmd.Parameters.AddWithValue("@" + tblCustomer.CustomerId, customer.CustomerId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            #endregion Initialization

            try
            {
                Int32 existKey = Convert.ToInt32(cmd.ExecuteScalar());
                if (existKey > 0)
                {
                    return true;
                }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Check Validation in Customer");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return false;
        }

        #endregion Public

        #endregion Static methods
    }
}
